import pymysql

# 创建到数据库的连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='tedu.cn',
    db='nsd2021',
    charset='utf8mb4'
)
# 创建游标。游标类似于文件对象，通过文件对象读写文件，通过游标操作数据库
cursor = conn.cursor()

###################################################
# 编写sql语句
# sql1 = 'INSERT INTO departments VALUES(%s, %s)'
# 通过游标执行sql语句
# 插入一行记录
# cursor.execute(sql1, (1, '人事部'))
# 插入多行记录
# cursor.executemany(sql1, [(2, '运维部'), (3, '开发部'), (4, '测试部'), (5, '财务部')])
###################################################
# 查询
# sql1 = 'SELECT * FROM departments'
# 通过游标执行sql语句
# cursor.execute(sql1)
# result1 = cursor.fetchone()    # 取出一条记录
# result2 = cursor.fetchmany(2)  # 继续取出2条记录
# result3 = cursor.fetchall()    # 继续取出剩余所有记录
# print(result1)
# print('*' * 30)
# print(result2)
# print('*' * 30)
# print(result3)
###################################################
# 修改
# sql1 = 'UPDATE departments SET dep_name=%s WHERE dep_name=%s'
# cursor.execute(sql1, ('人力资源部', '人事部'))
# 查询修改后的结果
# sql2 = 'SELECT * FROM departments WHERE dep_name=%s'
# cursor.execute(sql2, ('人力资源部',))
# print(cursor.fetchall())
###################################################
# 删除
sql1 = 'DELETE FROM departments WHERE id=%s'
cursor.execute(sql1, (5,))


###################################################

# 确认更改
conn.commit()
# 关闭
cursor.close()
conn.close()
